﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for MenuChild
/// </summary>
/// 

namespace BusLogic
{
    public class MenuChild
    {
        public static DataSet get_ALL_MENUCHILD()
        {
            DataSet catData = new DataSet();

            String connString = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;

            using (SqlConnection Conn = new SqlConnection(connString))
            {
                try
                {
                    String strCommandText;
                    SqlCommand objCommand;

                    strCommandText = @"SELECT MENUCHILD.ID, MENUCHILD.NAME, MENUPARENT.NAME AS PARENT, MENUCHILD.URL, MENUCHILD.[TARGET], 
                                    MENUCHILD.SORTED, MENUCHILD.RECORD_STATUS
                                    FROM MENUCHILD INNER JOIN MENUPARENT
                                    ON MENUCHILD.PARENTID = MENUPARENT.ID
                                    ORDER BY PARENT ASC, MENUCHILD.SORTED ASC";

                    objCommand = new SqlCommand(strCommandText, Conn);
                    objCommand.CommandType = CommandType.Text;

                    SqlDataAdapter adap = new SqlDataAdapter(objCommand);
                    Conn.Open();
                    adap.Fill(catData);
                    Conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }

                finally
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                    }
                }
            }

            return catData;
        }

        public static void remove_MENUCHILD(int iID)
        {
            string connStr = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;
            SqlConnection connection = new SqlConnection(connStr);
            try
            {
                string strCommandName;
                SqlCommand objCommand;
                SqlParameter objParam;

                strCommandName = "DELETE FROM MENUCHILD WHERE ID=@_ID";
                objCommand = new SqlCommand(strCommandName, connection);
                objCommand.CommandType = CommandType.Text;
                //Set the Parameters

                objParam = objCommand.Parameters.Add(new SqlParameter("@_ID", SqlDbType.Int));
                objParam.Direction = ParameterDirection.Input;
                objParam.Value = iID;

                connection.Open();
                objCommand.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        public static DataSet get_MENUCHILD(int iID)
        {
            DataSet retData = new DataSet();
            String connString = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ConnectionString;

            using (SqlConnection Conn = new SqlConnection(connString))
            {
                try
                {
                    String strCommandText;
                    SqlCommand objCommand;

                    strCommandText = "SELECT * FROM MENUCHILD WHERE ID = " + iID.ToString();

                    objCommand = new SqlCommand(strCommandText, Conn);
                    objCommand.CommandType = CommandType.Text;

                    SqlDataAdapter adap = new SqlDataAdapter(objCommand);
                    Conn.Open();
                    adap.Fill(retData, "Table");
                    Conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Conn.State == ConnectionState.Open)
                        Conn.Close();
                }
            }
            return retData;
        }
        

        public static void add_MENUCHILD(string sName, int iParentID, string sUrl, string sTarget, int iSorted, bool bRecordStatus)
        {
            string sConnect = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ToString();
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdAddProduct = new SqlCommand("sp_ADD_MENUCHILD", conn);
                    cmdAddProduct.CommandType = CommandType.StoredProcedure;

                    SqlParameter sqlPara;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@NAME", SqlDbType.NVarChar, 200));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sName;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@PARENTID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iParentID;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@URL", SqlDbType.NVarChar, 500));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sUrl;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@TARGET", SqlDbType.NVarChar, 50));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sTarget;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SORTED", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iSorted;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@RECORD_STATUS", SqlDbType.Bit));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = bRecordStatus;

                    cmdAddProduct.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
        }

        public static void update_MENUCHILD(int iID, string sName, int iParentID, string sUrl, string sTarget, int iSorted, bool bRecordStatus)
        {
            string sConnect = ConfigurationManager.ConnectionStrings["DBDIENLANH"].ToString();
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdAddProduct = new SqlCommand("sp_UPDATE_MENUCHILD", conn);
                    cmdAddProduct.CommandType = CommandType.StoredProcedure;

                    SqlParameter sqlPara;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iID;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@NAME", SqlDbType.NVarChar, 200));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sName;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@PARENTID", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iParentID;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@URL", SqlDbType.NVarChar, 500));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sUrl;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@TARGET", SqlDbType.NVarChar, 50));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = sTarget;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@SORTED", SqlDbType.Int));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = iSorted;

                    sqlPara = cmdAddProduct.Parameters.Add(new SqlParameter("@RECORD_STATUS", SqlDbType.Bit));
                    sqlPara.Direction = ParameterDirection.Input;
                    sqlPara.Value = bRecordStatus;

                    cmdAddProduct.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
        }
    }

}
